import pymysql
import pandas as pd
import logging
from sqlalchemy import create_engine, text
from sqlalchemy.dialects import mysql

from biz.entity.review_entity import MergeRequestReviewEntity, PushReviewEntity

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')


class ReviewService:
    # MySQL 连接配置
    DB_CONFIG = {
    "host": "127.0.0.",
    "port": 3307,
    "user": "root",
    "password": "123456",
    "database": "aicode",
    "charset": "utf8mb4"
    }

 # 构建数据库连接 URI
    db_uri = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}?charset=utf8mb4"
    engine = create_engine(db_uri, pool_pre_ping=True, pool_size=10, max_overflow=20)




    @staticmethod
    def init_db():
        """初始化数据库及表结构"""
        try:
            # with pymysql.connect(**ReviewService.DB_CONFIG) as conn:
            #     cursor = conn.cursor()
            #     # 创建 mr_review_log 表
            #     cursor.execute('''
                               

            with ReviewService.engine.connect() as conn:
                # 创建 mr_review_log 表
                conn.execute(text('''                               
                     CREATE TABLE IF NOT EXISTS mr_review_log (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        project_name VARCHAR(255),
                        author VARCHAR(255),
                        source_branch VARCHAR(255),
                        target_branch VARCHAR(255),
                        updated_at BIGINT,
                        commit_messages TEXT,
                        score INT,
                        url VARCHAR(255),
                        review_result VARCHAR(255),
                        additions INT DEFAULT 0,
                        deletions INT DEFAULT 0
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
                '''))
                # 创建 push_review_log 表
                conn.execute(text('''
                     CREATE TABLE IF NOT EXISTS push_review_log (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        project_name VARCHAR(255),
                        author VARCHAR(255),
                        branch VARCHAR(255),
                        updated_at BIGINT,
                        commit_messages TEXT,
                        score INT,
                        review_result VARCHAR(255),
                        additions INT DEFAULT 0,
                        deletions INT DEFAULT 0
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                '''))

                # 检查并添加缺失的列 (additions, deletions)
                tables = ["mr_review_log", "push_review_log"]
                columns_to_add = ["additions", "deletions"]

                for table in tables:
                    # # 查询当前表的所有列
                    # cursor.execute(f"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table}' AND TABLE_SCHEMA = '{ReviewService.DB_CONFIG['database']}'")
                    # current_columns = [col[0] for col in cursor.fetchall()]

                    result = conn.execute(text(f"""
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE TABLE_NAME = '{table}' AND TABLE_SCHEMA = '{ReviewService.DB_CONFIG['database']}'
                    """))
                    current_columns = [row[0] for row in result.fetchall()]



                    for column in columns_to_add:
                        if column not in current_columns:
                            conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {column} INTEGER DEFAULT 0"))

                        # 创建常用查询字段的索引
                    # index_sqls = [
                    #     text("CREATE INDEX IF NOT EXISTS idx_mr_author ON mr_review_log(author)"),
                    #     text("CREATE INDEX IF NOT EXISTS idx_mr_project ON mr_review_log(project_name)"),
                    #     text("CREATE INDEX IF NOT EXISTS idx_mr_time ON mr_review_log(updated_at)"),
                        
                    #     text("CREATE INDEX IF NOT EXISTS idx_push_author ON push_review_log(author)"),
                    #     text("CREATE INDEX IF NOT EXISTS idx_push_project ON push_review_log(project_name)"),
                    #     text("CREATE INDEX IF NOT EXISTS idx_push_time ON push_review_log(updated_at)")
                    # ]
                    # for sql in index_sqls:
                    #     conn.execute(sql)

                    conn.commit()


        except Exception as e:
            logging.error(f"Database initialization failed: {e}")
            print(f"Database initialization failed: {e}")

        # except pymysql.MySQLError as e:
        #     print(f"Database initialization failed: {e}")

    @staticmethod
    def insert_mr_review_log(entity: MergeRequestReviewEntity):
        """插入合并请求审核日志"""
        try:
            #with pymysql.connect(**ReviewService.DB_CONFIG) as conn:
            with ReviewService.engine.connect() as conn:
                # cursor = conn.cursor()
                # conn.execute(text('''
                #     INSERT INTO mr_review_log 
                #     (project_name, author, source_branch, target_branch, updated_at, commit_messages, score, url, review_result, additions, deletions)
                #     VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                # ''', (
                #     entity.project_name, entity.author, entity.source_branch,
                #     entity.target_branch, entity.updated_at, entity.commit_messages,
                #     entity.score, entity.url, entity.review_result, entity.additions, entity.deletions
                # )))

                sql = text('''
                        INSERT INTO mr_review_log 
                        (project_name, author, source_branch, target_branch, updated_at, commit_messages, score, url, review_result, additions, deletions)
                        VALUES (
                            :project_name, :author, :source_branch, :target_branch,
                            :updated_at, :commit_messages, :score, :url,
                            :review_result, :additions, :deletions
                        )
                    ''')   
                params = {
                        "project_name": entity.project_name,
                        "author": entity.author,
                        "source_branch": entity.source_branch,
                        "target_branch": entity.target_branch,
                        "updated_at": entity.updated_at,
                        "commit_messages": entity.commit_messages,
                        "score": entity.score,
                        "url": entity.url,
                        "review_result": entity.review_result,
                        "additions": entity.additions,
                        "deletions": entity.deletions
                    }
                  
                compiled_sql = sql.compile(dialect=mysql.dialect())

                print("Compiled SQL:", compiled_sql)
                    
                print("Params:", params)

                result = conn.execute(sql, params)

                conn.commit()
        except Exception  as e:
            print(f"Error inserting MR review log: {e}")
            logging.error(f"insert_mr_review_log   failed: {e}")
            conn.rollback()

    @staticmethod
    def get_mr_review_logs(authors: list = None, project_names: list = None, updated_at_gte: int = None, updated_at_lte: int = None) -> pd.DataFrame:
        """获取符合条件的合并请求审核日志"""
        try:
            #with pymysql.connect(**ReviewService.DB_CONFIG) as conn:
                query = """
                    SELECT project_name, author, source_branch, target_branch, updated_at, commit_messages, score, url, review_result, additions, deletions
                    FROM mr_review_log
                    WHERE 1=1
                """
                params = []

                if authors:
                    placeholders = ','.join(['%s'] * len(authors))
                    query += f" AND author IN ({placeholders})"
                    params.extend(authors)

                if project_names:
                    placeholders = ','.join(['%s'] * len(project_names))
                    query += f" AND project_name IN ({placeholders})"
                    params.extend(project_names)

                if updated_at_gte is not None:
                    query += " AND updated_at >= %s"
                    params.append(updated_at_gte)

                if updated_at_lte is not None:
                    query += " AND updated_at <= %s"
                    params.append(updated_at_lte)

                query += " ORDER BY updated_at DESC"

               # df = pd.read_sql_query(sql=query, con=conn, params=params)

                # 使用 SQLAlchemy engine 执行查询并返回 DataFrame
                with ReviewService.engine.connect() as conn:
                    df = pd.read_sql_query(sql=query, con=conn, params=tuple(params))
                    return df

                #return df
        except Exception  as e:
            print(f"Error retrieving MR review logs: {e}")
            return pd.DataFrame()

    @staticmethod
    def insert_push_review_log(entity: PushReviewEntity):
        """插入推送审核日志"""
        try:
            #with pymysql.connect(**ReviewService.DB_CONFIG) as conn:
            with ReviewService.engine.connect() as conn:
                # cursor = conn.cursor()
                # conn.execute(text('''
                #     INSERT INTO push_review_log 
                #     (project_name, author, branch, updated_at, commit_messages, score, review_result, additions, deletions)
                #     VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                # ''', (
                #     entity.project_name, entity.author, entity.branch,
                #     entity.updated_at, entity.commit_messages, entity.score,
                #     entity.review_result, entity.additions, entity.deletions
                # )))
                sql = text('''
                    INSERT INTO push_review_log 
                    (project_name, author, branch, updated_at, commit_messages, score, review_result, additions, deletions)
                    VALUES (:project_name, :author, :branch, :updated_at, :commit_messages, :score, :review_result, :additions, :deletions)
                    ''')
                params = {
                    "project_name": entity.project_name,
                    "author": entity.author,
                    "branch": entity.branch,
                    "updated_at": entity.updated_at,
                    "commit_messages": entity.commit_messages,
                    "score": entity.score,
                    "review_result": entity.review_result,
                    "additions": entity.additions,
                    "deletions": entity.deletions
                }
                compiled_sql = sql.compile(dialect=mysql.dialect())
                print("Compiled SQL:", compiled_sql)
                print("Params:", params)

                result = conn.execute(sql, params)
                conn.commit()
        except Exception as e:
            print(f"Error inserting push review log: {e}")            
            conn.rollback()

    @staticmethod
    def get_push_review_logs(authors: list = None, project_names: list = None, updated_at_gte: int = None, updated_at_lte: int = None) -> pd.DataFrame:
        """获取符合条件的推送审核日志"""
        try:
            # with pymysql.connect(**ReviewService.DB_CONFIG) as conn:
                query = """
                    SELECT project_name, author, branch, updated_at, commit_messages, score, review_result, additions, deletions
                    FROM push_review_log
                    WHERE 1=1
                """
                params = []

                if authors:
                    placeholders = ','.join(['%s'] * len(authors))
                    query += f" AND author IN ({placeholders})"
                    params.extend(authors)

                if project_names:
                    placeholders = ','.join(['%s'] * len(project_names))
                    query += f" AND project_name IN ({placeholders})"
                    params.extend(project_names)

                if updated_at_gte is not None:
                    query += " AND updated_at >= %s"
                    params.append(updated_at_gte)

                if updated_at_lte is not None:
                    query += " AND updated_at <= %s"
                    params.append(updated_at_lte)

                query += " ORDER BY updated_at DESC"

                #df = pd.read_sql_query(sql=query, con=conn, params=params)
                #return df
                with ReviewService.engine.connect() as conn:
                        df = pd.read_sql_query(sql=query, con=conn, params=tuple(params))
                        return df

        except Exception  as e:
            print(f"Error retrieving push review logs: {e}")
            return pd.DataFrame()


# Initialize database
ReviewService.init_db()